/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.eng.scc.core.schema;

import com.eng.scc.core.schema.fetcher.AddressFetcher;
import com.eng.scc.core.schema.fetcher.CityFetcher;
import com.eng.scc.core.schema.fetcher.CompanyFetcher;
import com.eng.scc.core.schema.fetcher.DriverFetcher;
import com.eng.scc.core.schema.fetcher.FillingFetcher;
import com.eng.scc.core.schema.fetcher.FreightFetcher;
import com.eng.scc.core.schema.fetcher.DeviceFetcher;
import com.eng.scc.core.schema.fetcher.DeviceInfoFetcher;
import com.eng.scc.core.schema.fetcher.HomeStateFetcher;
import com.eng.scc.core.schema.fetcher.LoginFetcher;
import com.eng.scc.core.schema.fetcher.PeopleFetcher;
import com.eng.scc.core.schema.fetcher.VehicleFetcher;

/**
 *
 * @author rd
 */
public class Schema
{
    static final String COMMA = ",";

    public static class Addresses
    {
        private Addresses(){}
        
        public static final String NAME = "core_addresses";
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                             Fields.ID + Schema.COMMA + 
                                             Fields.REF_CITY + Schema.COMMA +
                                             Fields.REF_HOME_STATE + Schema.COMMA +
                                             Fields.COUNTRY + Schema.COMMA +
                                             Fields.ADDRESS + Schema.COMMA +
                                             Fields.NUMBER + Schema.COMMA +
                                             Fields.CEP + ") VALUES (?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_CITY + " = ? " + COMMA +
                                             Fields.REF_HOME_STATE + " = ? " + COMMA +
                                             Fields.COUNTRY + " = ? " + COMMA +
                                             Fields.ADDRESS + " = ? " + COMMA +
                                             Fields.NUMBER + " = ? " + COMMA +
                                             Fields.CEP + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_CITY + COMMA +
                                            Fields.REF_HOME_STATE + COMMA +
                                            Fields.COUNTRY + COMMA +
                                            Fields.ADDRESS + COMMA +
                                            Fields.NUMBER + COMMA +
                                            Fields.CEP +
                                            " FROM " + NAME;
        
        public static AddressFetcher fetcher = new AddressFetcher();
        
        public static Addresses table = new Addresses();
        
//    +----------------+--------------+------+-----+---------+-------+
//    | Field          | Type         | Null | Key | Default | Extra |
//    +----------------+--------------+------+-----+---------+-------+
//    | id             | int(11)      | NO   | PRI | NULL    |       |
//    | ref_city       | int(11)      | NO   | MUL | NULL    |       |
//    | ref_home_state | int(11)      | NO   | MUL | NULL    |       |
//    | country        | varchar(200) | YES  |     | NULL    |       |
//    | address        | varchar(100) | YES  |     | NULL    |       |
//    | number         | varchar(45)  | YES  |     | NULL    |       |
//    | cep            | varchar(45)  | YES  |     | NULL    |       |
//    +----------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =             Addresses.NAME + ".id";
            public static final String REF_CITY =       Addresses.NAME + ".ref_city";
            public static final String REF_HOME_STATE = Addresses.NAME + ".ref_home_state";
            public static final String COUNTRY =        Addresses.NAME + ".country";
            public static final String ADDRESS =        Addresses.NAME + ".address";
            public static final String NUMBER =         Addresses.NAME + ".number";
            public static final String CEP =            Addresses.NAME + ".cep";
        }
    }
    
    public static class Cities
    {
        private Cities(){}
        
        public static final String NAME = "core_cities";
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                             Fields.ID + Schema.COMMA +
                                             Fields.REF_HOME_STATE + Schema.COMMA + 
                                             Fields.NAME + ") VALUES (?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.NAME + " = ? " + COMMA +
                                             Fields.REF_HOME_STATE + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_HOME_STATE + COMMA +
                                            Fields.NAME +
                                            " FROM " + NAME;
        
        public static CityFetcher fetcher = new CityFetcher();
        
        public static Cities table = new Cities();
        
//        +-------+--------------+------+-----+---------+-------+
//        | Field | Type         | Null | Key | Default | Extra |
//        +-------+--------------+------+-----+---------+-------+
//        | id    | int(11)      | NO   | PRI | NULL    |       |
//        | name  | varchar(200) | YES  |     | NULL    |       |
//        +-------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID             = Cities.NAME + ".id";
            public static final String REF_HOME_STATE = Cities.NAME + ".ref_home_state";
            public static final String NAME           = Cities.NAME + ".name";
        }
    }
    
    public static class Companies
    {
        private Companies(){}
        
        public static final String NAME = "core_companies";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                             Fields.ID + COMMA +
                                             Fields.REF_ADDRESS + COMMA +
                                             Fields.REAL_NAME + COMMA +
                                             Fields.FANTASY_NAME + COMMA +
                                             Fields.CNPJ + COMMA +
                                             Fields.EMAIL + COMMA +
                                             Fields.PHONE_1 + COMMA +
                                             Fields.PHONE_2 + COMMA +
                                             Fields.STATE + ") VALUES (?,?,?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_ADDRESS + " = ? " + COMMA +
                                             Fields.REAL_NAME + " = ? " + COMMA +
                                             Fields.FANTASY_NAME + " = ? " + COMMA +
                                             Fields.CNPJ + " = ? " + COMMA +
                                             Fields.EMAIL + " = ? " + COMMA +
                                             Fields.PHONE_1 + " = ? " + COMMA +
                                             Fields.PHONE_2 + " = ? " + COMMA +
                                             Fields.STATE + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_ADDRESS + COMMA +
                                            Fields.REAL_NAME + COMMA +
                                            Fields.FANTASY_NAME + COMMA +
                                            Fields.CNPJ + COMMA +
                                            Fields.EMAIL + COMMA +
                                            Fields.PHONE_1 + COMMA +
                                            Fields.PHONE_2 + COMMA +
                                            Fields.STATE +
                                            " FROM " + NAME;
        
        public static CompanyFetcher fetcher = new CompanyFetcher();
        
        public static Companies table = new Companies();
        
//+--------------+--------------+------+-----+---------+-------+
//| Field        | Type         | Null | Key | Default | Extra |
//+--------------+--------------+------+-----+---------+-------+
//| id           | int(11)      | NO   | PRI | NULL    |       |
//| ref_address  | int(11)      | NO   | MUL | NULL    |       |
//| real_name    | varchar(400) | YES  |     | NULL    |       |
//| fantasy_name | varchar(400) | YES  |     | NULL    |       |
//| cnpj         | varchar(45)  | YES  |     | NULL    |       |
//| email        | varchar(80)  | YES  |     | NULL    |       |
//| phone_1      | varchar(45)  | YES  |     | NULL    |       |
//| phone_2      | varchar(45)  | YES  |     | NULL    |       |
//| state        | smallint(6)  | YES  |     | NULL    |       |
//+--------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =           Companies.NAME + ".id";
            public static final String REF_ADDRESS =  Companies.NAME + ".ref_address";
            public static final String REAL_NAME =    Companies.NAME + ".real_name";
            public static final String FANTASY_NAME = Companies.NAME + ".fantasy_name";
            public static final String CNPJ =         Companies.NAME + ".cnpj";
            public static final String EMAIL =        Companies.NAME + ".email";
            public static final String PHONE_1 =      Companies.NAME + ".phone_1";
            public static final String PHONE_2 =      Companies.NAME + ".phone_2";
            public static final String STATE =        Companies.NAME + ".state";
        }
    }
    
    public static class Drivers
    {
        private Drivers(){}
        
        public static final String NAME = "core_drivers";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                             Fields.ID + COMMA +
                                            Fields.REF_PEOPLE + COMMA +
                                            Fields.REF_COMPANY + COMMA +
                                            Fields.CNH + COMMA +
                                            Fields.CNH_DUE_DATE + COMMA +
                                            Fields.CNH_CATEGORY + COMMA +
                                            Fields.DEFAULT_COMMISSION + COMMA +
                                            Fields.STATE +
                                            ") VALUES (?,?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_PEOPLE + " = ? " + COMMA +
                                             Fields.REF_COMPANY + " = ? " + COMMA +
                                             Fields.CNH + " = ? " + COMMA +
                                             Fields.CNH_DUE_DATE + " = ? " + COMMA +
                                             Fields.CNH_CATEGORY + " = ? " + COMMA +
                                             Fields.DEFAULT_COMMISSION + " = ? " + COMMA +
                                             Fields.STATE + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_PEOPLE + COMMA +
                                            Fields.REF_COMPANY + COMMA +
                                            Fields.CNH + COMMA +
                                            Fields.CNH_DUE_DATE + COMMA +
                                            Fields.CNH_CATEGORY + COMMA +
                                            Fields.DEFAULT_COMMISSION + COMMA +
                                            Fields.STATE +
                                            " FROM " + NAME;
        
        public static DriverFetcher fetcher = new DriverFetcher();
        
        public static Drivers table = new Drivers();
        
//+--------------------+-------------+------+-----+---------+-------+
//| Field              | Type        | Null | Key | Default | Extra |
//+--------------------+-------------+------+-----+---------+-------+
//| id                 | int(11)     | NO   | PRI | NULL    |       |
//| ref_people         | int(11)     | NO   | MUL | NULL    |       |
//| ref_company        | int(11)     | NO   | MUL | NULL    |       |
//| cnh                | varchar(45) | YES  |     | NULL    |       |
//| cnh_due_date       | date        | YES  |     | NULL    |       |
//| cnh_category       | varchar(45) | YES  |     | NULL    |       |
//| default_commission | double      | YES  |     | NULL    |       |
//| state              | smallint(6) | YES  |     | NULL    |       |
//+--------------------+-------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =                 Drivers.NAME + ".id";
            public static final String REF_PEOPLE =         Drivers.NAME + ".ref_people";
            public static final String REF_COMPANY =        Drivers.NAME + ".ref_company";
            public static final String CNH =                Drivers.NAME + ".cnh";
            public static final String CNH_DUE_DATE =       Drivers.NAME + ".cnh_due_date";
            public static final String CNH_CATEGORY =       Drivers.NAME + ".cnh_category";
            public static final String DEFAULT_COMMISSION = Drivers.NAME + ".default_commission";
            public static final String STATE =              Drivers.NAME + ".state";
        }
    }
    
    public static class Fillings
    {
        private Fillings(){}
        
        public static final String NAME = "core_fillings";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                             Fields.ID + COMMA +
                                            Fields.REF_FREIGHT + COMMA +
                                            Fields.QUANTITY + COMMA +
                                            Fields.KM + COMMA +
                                            Fields.LOCALE + COMMA +
                                            Fields.DATE + COMMA +
                                            Fields.AVG + COMMA +
                                            Fields.PRICE_PER_LITER + ") VALUES (?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_FREIGHT + " = ? " + COMMA +
                                             Fields.QUANTITY + " = ? " + COMMA +
                                             Fields.KM + " = ? " + COMMA +
                                             Fields.LOCALE + " = ? " + COMMA +
                                             Fields.DATE + " = ? " + COMMA +
                                             Fields.AVG + " = ? " + COMMA +
                                             Fields.PRICE_PER_LITER + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_FREIGHT + COMMA +
                                            Fields.QUANTITY + COMMA +
                                            Fields.KM + COMMA +
                                            Fields.LOCALE + COMMA +
                                            Fields.DATE + COMMA +
                                            Fields.AVG + COMMA +
                                            Fields.PRICE_PER_LITER +
                                            " FROM " + NAME;
        
        public static FillingFetcher fetcher = new FillingFetcher();
        
        public static Fillings table = new Fillings();
        
//+-------------+--------------+------+-----+---------+-------+
//| Field       | Type         | Null | Key | Default | Extra |
//+-------------+--------------+------+-----+---------+-------+
//| id          | int(11)      | NO   | PRI | NULL    |       |
//| ref_freight | int(11)      | NO   | MUL | NULL    |       |
//| quantity    | double       | YES  |     | NULL    |       |
//| km          | bigint(20)   | YES  |     | NULL    |       |
//| locale      | varchar(300) | YES  |     | NULL    |       |
//| date        | datetime     | YES  |     | NULL    |       |
//| avg         | double       | YES  |     | NULL    |       |
//+-------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =              Fillings.NAME + ".id";
            public static final String REF_FREIGHT =     Fillings.NAME + ".ref_freight";
            public static final String QUANTITY =        Fillings.NAME + ".quantity";
            public static final String KM =              Fillings.NAME + ".km";
            public static final String LOCALE =          Fillings.NAME + ".locale";
            public static final String DATE =            Fillings.NAME + ".date";
            public static final String AVG =             Fillings.NAME + ".avg";
            public static final String PRICE_PER_LITER = Fillings.NAME + ".price_per_liter";
        }
    }
    
    public static class Freights
    {
        private Freights(){}
        
        public static final String NAME = "core_freights";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                             Fields.ID + COMMA +
                                            Fields.REF_VEHICLE + COMMA +
                                            Fields.REF_DRIVER + COMMA +
                                            Fields.DT_DEPARTURE + COMMA +
                                            Fields.DT_ARRIVAL + COMMA +
                                            Fields.COMISSION + COMMA +
                                            Fields.KM_INITIAL + COMMA +
                                            Fields.KM_END + COMMA +
                                            Fields.FUEL_PREVISION + COMMA +
                                            Fields.STATE + COMMA +
                                            Fields.TOTAL_FILLINGS +
                                            ") VALUES (?,?,?,?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_VEHICLE + " = ? " + COMMA +
                                             Fields.REF_DRIVER + " = ? " + COMMA +
                                             Fields.DT_DEPARTURE + " = ? " + COMMA +
                                             Fields.DT_ARRIVAL + " = ? " + COMMA +
                                             Fields.COMISSION + " = ? " + COMMA +
                                             Fields.KM_INITIAL + " = ? " + COMMA +
                                             Fields.KM_END + " = ? " + COMMA +
                                             Fields.FUEL_PREVISION + " = ? " + COMMA +
                                             Fields.STATE + COMMA +
                                             Fields.TOTAL_FILLINGS + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_VEHICLE + COMMA +
                                            Fields.REF_DRIVER + COMMA +
                                            Fields.DT_DEPARTURE + COMMA +
                                            Fields.DT_ARRIVAL + COMMA +
                                            Fields.COMISSION + COMMA +
                                            Fields.KM_INITIAL + COMMA +
                                            Fields.KM_END + COMMA +
                                            Fields.FUEL_PREVISION + COMMA +
                                            Fields.STATE + COMMA +
                                            Fields.TOTAL_FILLINGS +
                                            " FROM " + NAME;
        
        public static FreightFetcher fetcher = new FreightFetcher();
        
        public static Freights table = new Freights();
        
//+----------------+------------+------+-----+---------+-------+
//| Field          | Type       | Null | Key | Default | Extra |
//+----------------+------------+------+-----+---------+-------+
//| id             | int(11)    | NO   | PRI | NULL    |       |
//| ref_vehicle    | int(11)    | NO   | MUL | NULL    |       |
//| ref_driver     | int(11)    | NO   | MUL | NULL    |       |
//| dt_departure   | datetime   | YES  |     | NULL    |       |
//| dt_arrival     | datetime   | YES  |     | NULL    |       |
//| comission      | double     | YES  |     | NULL    |       |
//| km_initial     | bigint(20) | YES  |     | NULL    |       |
//| km_end         | bigint(20) | YES  |     | NULL    |       |
//| fuel_prevision | double     | YES  |     | NULL    |       |
//| state          | int(11)    | YES  |     | NULL    |       |
//+----------------+------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =             Freights.NAME + ".id";
            public static final String REF_VEHICLE =    Freights.NAME + ".ref_vehicle";
            public static final String REF_DRIVER =     Freights.NAME + ".ref_driver";
            public static final String DT_DEPARTURE =   Freights.NAME + ".dt_departure";
            public static final String DT_ARRIVAL =     Freights.NAME + ".dt_arrival";
            public static final String COMISSION =      Freights.NAME + ".comission";
            public static final String KM_INITIAL =     Freights.NAME + ".km_initial";
            public static final String KM_END =         Freights.NAME + ".km_end";
            public static final String FUEL_PREVISION = Freights.NAME + ".fuel_prevision";
            public static final String STATE =          Freights.NAME + ".state";
            public static final String TOTAL_FILLINGS =          Freights.NAME + ".total_fillings";
        }
    }
    
    public static class DeviceInfos
    {
        private DeviceInfos(){}
        
        public static final String NAME = "core_device_infos";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                            Fields.ID + COMMA +
                                            Fields.REF_DEVICE + COMMA +
                                            Fields.INFO + COMMA +
                                            Fields.DT_TRANSMITION +
                                            ") VALUES (?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_DEVICE + " = ? " + COMMA +
                                             Fields.INFO + " = ? " + COMMA +
                                             Fields.DT_TRANSMITION + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_DEVICE + COMMA +
                                            Fields.INFO + COMMA +
                                            Fields.DT_TRANSMITION +
                                            " FROM " + NAME;
        
        public static DeviceInfoFetcher fetcher = new DeviceInfoFetcher();
        
        public static DeviceInfos table = new DeviceInfos();
        
//+----------------+--------------+------+-----+---------+-------+
//| Field          | Type         | Null | Key | Default | Extra |
//+----------------+--------------+------+-----+---------+-------+
//| id             | int(11)      | NO   | PRI | NULL    |       |
//| ref_device   | int(11)      | NO   | MUL | NULL    |       |
//| info           | varchar(400) | YES  |     | NULL    |       |
//| dt_transmition | datetime     | YES  |     | NULL    |       |
//+----------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =             DeviceInfos.NAME + ".id";
            public static final String REF_DEVICE =     DeviceInfos.NAME + ".ref_device";
            public static final String INFO =           DeviceInfos.NAME + ".info";
            public static final String DT_TRANSMITION = DeviceInfos.NAME + ".dt_transmition";
        }
    }
    
    public static class Devices
    {
        private Devices(){}
        
        public static final String NAME = "core_devices";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                            Fields.ID + COMMA +
                                            Fields.REF_VEHICLE + COMMA +
                                            Fields.SERIAL + COMMA +
                                            Fields.DESCRIPTION + COMMA +
                                            Fields.DT_INSTALLATION + COMMA +
                                            Fields.STATUS +
                                            ") VALUES (?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_VEHICLE + " = ? " + COMMA +
                                             Fields.SERIAL + " = ? " + COMMA +
                                             Fields.DESCRIPTION + " = ? " + COMMA +
                                             Fields.DT_INSTALLATION + " = ? " + COMMA + 
                                             Fields.STATUS + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_VEHICLE + COMMA +
                                            Fields.SERIAL + COMMA +
                                            Fields.DESCRIPTION + COMMA +
                                            Fields.DT_INSTALLATION + COMMA +
                                            Fields.STATUS +
                                            " FROM " + NAME;
        
        public static DeviceFetcher fetcher = new DeviceFetcher();
        
        public static Devices table = new Devices();
        
//+-----------------+--------------+------+-----+---------+-------+
//| Field           | Type         | Null | Key | Default | Extra |
//+-----------------+--------------+------+-----+---------+-------+
//| id              | int(11)      | NO   | PRI | NULL    |       |
//| ref_vehicle     | int(11)      | NO   | MUL | NULL    |       |
//| serial          | varchar(45)  | YES  |     | NULL    |       |
//| description     | varchar(200) | YES  |     | NULL    |       |
//| dt_installation | date         | YES  |     | NULL    |       |
//+-----------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =              Devices.NAME + ".id";
            public static final String REF_VEHICLE =     Devices.NAME + ".ref_vehicle";
            public static final String SERIAL =          Devices.NAME + ".serial";
            public static final String DESCRIPTION =     Devices.NAME + ".description";
            public static final String DT_INSTALLATION = Devices.NAME + ".dt_installation";
            public static final String STATUS =          Devices.NAME + ".status";
        }
    }
    
    public static class HomeStates
    {
        private HomeStates(){}
        
        public static final String NAME = "core_home_states";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                             Fields.ID + Schema.COMMA +
                                             Fields.NAME + ") VALUES (?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.NAME + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.NAME +
                                            " FROM " + NAME;
        
        public static HomeStateFetcher fetcher = new HomeStateFetcher();
        
        public static Cities table = new Cities();
        
//        +-------+--------------+------+-----+---------+-------+
//        | Field | Type         | Null | Key | Default | Extra |
//        +-------+--------------+------+-----+---------+-------+
//        | id    | int(11)      | NO   | PRI | NULL    |       |
//        | name  | varchar(200) | YES  |     | NULL    |       |
//        +-------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =   HomeStates.NAME + ".id";
            public static final String NAME = HomeStates.NAME + ".name";
        }
    }
    
    public static class Peoples
    {
        private Peoples(){}
        
        public static final String NAME = "core_peoples";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                            Fields.ID + COMMA +
                                            Fields.REF_ADDRESS + COMMA +
                                            Fields.NAME + COMMA +
                                            Fields.EMAIL + COMMA +
                                            Fields.CPF + COMMA +
                                            Fields.RG + COMMA +
                                            Fields.DT_BIRTHDAY + COMMA +
                                            Fields.GENDER + COMMA +
                                            Fields.PHONE_FIX + COMMA +
                                            Fields.PHONE_MOBILE + COMMA +
                                            Fields.STATE +
                                            ") VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_ADDRESS + " = ? " + COMMA +
                                             Fields.NAME + " = ? " + COMMA +
                                             Fields.EMAIL + " = ? " + COMMA +
                                             Fields.CPF + " = ? " + COMMA +
                                             Fields.RG + " = ? " + COMMA +
                                             Fields.DT_BIRTHDAY + " = ? " + COMMA +
                                             Fields.GENDER + " = ? " + COMMA +
                                             Fields.PHONE_FIX + " = ? " + COMMA +
                                             Fields.PHONE_MOBILE + " = ? " + COMMA +
                                             Fields.STATE + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_ADDRESS + COMMA +
                                            Fields.NAME + COMMA +
                                            Fields.EMAIL + COMMA +
                                            Fields.CPF + COMMA +
                                            Fields.RG + COMMA +
                                            Fields.DT_BIRTHDAY + COMMA +
                                            Fields.GENDER + COMMA +
                                            Fields.PHONE_FIX + COMMA +
                                            Fields.PHONE_MOBILE + COMMA +
                                            Fields.STATE +
                                            " FROM " + NAME;
        
        public static PeopleFetcher fetcher = new PeopleFetcher();
        
        public static Peoples table = new Peoples();
        
//+--------------+--------------+------+-----+---------+-------+
//| Field        | Type         | Null | Key | Default | Extra |
//+--------------+--------------+------+-----+---------+-------+
//| id           | int(11)      | NO   | PRI | NULL    |       |
//| ref_address  | int(11)      | NO   | MUL | NULL    |       |
//| name         | varchar(160) | NO   |     | NULL    |       |
//| email        | varchar(45)  | NO   |     | NULL    |       |
//| cpf          | varchar(45)  | NO   |     | NULL    |       |
//| rg           | varchar(45)  | YES  |     | NULL    |       |
//| dt_birthday  | date         | YES  |     | NULL    |       |
//| gender       | tinyint(4)   | YES  |     | NULL    |       |
//| phone_fix    | varchar(45)  | YES  |     | NULL    |       |
//| phone_mobile | varchar(45)  | YES  |     | NULL    |       |
//| state        | smallint(6)  | YES  |     | NULL    |       |
//+--------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =           Peoples.NAME + ".id";
            public static final String REF_ADDRESS =  Peoples.NAME + ".ref_address";
            public static final String NAME =         Peoples.NAME + ".name";
            public static final String EMAIL =        Peoples.NAME + ".email";
            public static final String CPF =          Peoples.NAME + ".cpf";
            public static final String RG =           Peoples.NAME + ".rg";
            public static final String DT_BIRTHDAY =  Peoples.NAME + ".dt_birthday";
            public static final String GENDER =       Peoples.NAME + ".gender";
            public static final String PHONE_FIX =    Peoples.NAME + ".phone_fix";
            public static final String PHONE_MOBILE = Peoples.NAME + ".phone_mobile";
            public static final String STATE =        Peoples.NAME + ".state";
        }
    }
    
    public static class Vehicles
    {
        private Vehicles(){}
        
        public static final String NAME = "core_vehicles";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                            Fields.ID + COMMA +
                                            Fields.REF_DRIVER + COMMA +
                                            Fields.REF_COMPANY + COMMA +
                                            Fields.BRAND + COMMA +
                                            Fields.MODEL + COMMA +
                                            Fields.PLATE + COMMA +
                                            Fields.MODEL_YEAR + COMMA +
                                            Fields.MANUFACTURING_YEAR + COMMA +
                                            Fields.COLOR + COMMA +
                                            Fields.CHASSI + COMMA +
                                            Fields.RENAVAM + COMMA +
                                            Fields.CAPACITY + COMMA +
                                            Fields.TRUCK_MODEL + COMMA +
                                            Fields.AXLES + COMMA +
                                            Fields.MAX_FUEL + COMMA +
                                            Fields.MANU_AVERANGE + COMMA +
                                            Fields.INFO + COMMA +
                                            Fields.STATE +
                                            ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_DRIVER + " = ? " + COMMA +
                                             Fields.REF_COMPANY + " = ? " + COMMA +
                                             Fields.BRAND + " = ? " + COMMA +
                                             Fields.MODEL + " = ? " + COMMA +
                                             Fields.PLATE + " = ? " + COMMA +
                                             Fields.MODEL_YEAR + " = ? " + COMMA +
                                             Fields.MANUFACTURING_YEAR + " = ? " + COMMA +
                                             Fields.COLOR + " = ? " + COMMA +
                                             Fields.CHASSI + " = ? " + COMMA +
                                             Fields.RENAVAM + " = ? " + COMMA +
                                             Fields.CAPACITY + " = ? " + COMMA +
                                             Fields.TRUCK_MODEL + " = ? " + COMMA +
                                             Fields.AXLES + " = ? " + COMMA +
                                             Fields.MAX_FUEL + " = ? " + COMMA +
                                             Fields.MANU_AVERANGE + " = ? " + COMMA +
                                             Fields.INFO + " = ? " + COMMA +
                                             Fields.STATE + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_DRIVER + COMMA +
                                            Fields.REF_COMPANY + COMMA +
                                            Fields.BRAND + COMMA +
                                            Fields.MODEL + COMMA +
                                            Fields.PLATE + COMMA +
                                            Fields.MODEL_YEAR + COMMA +
                                            Fields.MANUFACTURING_YEAR + COMMA +
                                            Fields.COLOR + COMMA +
                                            Fields.CHASSI + COMMA +
                                            Fields.RENAVAM + COMMA +
                                            Fields.CAPACITY + COMMA +
                                            Fields.TRUCK_MODEL + COMMA +
                                            Fields.AXLES + COMMA +
                                            Fields.MAX_FUEL + COMMA +
                                            Fields.MANU_AVERANGE + COMMA +
                                            Fields.INFO + COMMA +
                                            Fields.STATE +
                                            " FROM " + NAME;
        
        public static VehicleFetcher fetcher = new VehicleFetcher();
        
        public static Vehicles table = new Vehicles();
        
//+--------------------+--------------+------+-----+---------+-------+
//| Field              | Type         | Null | Key | Default | Extra |
//+--------------------+--------------+------+-----+---------+-------+
//| id                 | int(11)      | NO   | PRI | NULL    |       |
//| ref_driver         | int(11)      | NO   | MUL | NULL    |       |
//| ref_company        | int(11)      | NO   | MUL | NULL    |       |
//| manufacturer       | varchar(300) | YES  |     | NULL    |       |
//| brand             | varchar(300) | YES  |     | NULL    |       |
//| model              | varchar(300) | YES  |     | NULL    |       |
//| plate              | varchar(45)  | YES  |     | NULL    |       |
//| model_year         | int(11)      | YES  |     | NULL    |       |
//| manufacturing_year | int(11)      | YES  |     | NULL    |       |
//| color              | varchar(45)  | YES  |     | NULL    |       |
//| chassi             | varchar(400) | YES  |     | NULL    |       |
//| renavam            | varchar(100) | YES  |     | NULL    |       |
//| capacity           | double       | YES  |     | NULL    |       |
//| truck_model        | varchar(300) | YES  |     | NULL    |       |
//| axles              | int(11)      | YES  |     | NULL    |       |
//| max_fuel           | double       | YES  |     | NULL    |       |
//| manu_averange      | double       | YES  |     | NULL    |       |
//| info               | varchar(600) | YES  |     | NULL    |       |
//| status             | smallint(6)  | YES  |     | NULL    |       |
//+--------------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =                    Vehicles.NAME + ".id";
            public static final String REF_DRIVER =            Vehicles.NAME + ".ref_driver";
            public static final String REF_COMPANY =           Vehicles.NAME + ".ref_company";
            public static final String BRAND =                 Vehicles.NAME + ".brand";
            public static final String MODEL =                 Vehicles.NAME + ".model";
            public static final String PLATE =                 Vehicles.NAME + ".plate";
            public static final String MODEL_YEAR =            Vehicles.NAME + ".model_year";
            public static final String MANUFACTURING_YEAR =    Vehicles.NAME + ".manufacturing_year";
            public static final String COLOR =                 Vehicles.NAME + ".color";
            public static final String CHASSI =                Vehicles.NAME + ".chassi";
            public static final String RENAVAM =               Vehicles.NAME + ".renavam";
            public static final String CAPACITY =              Vehicles.NAME + ".capacity";
            public static final String TRUCK_MODEL =           Vehicles.NAME + ".truck_model";
            public static final String AXLES =                 Vehicles.NAME + ".axles";
            public static final String MAX_FUEL =              Vehicles.NAME + ".max_fuel";
            public static final String MANU_AVERANGE =         Vehicles.NAME + ".manu_averange";
            public static final String INFO =                  Vehicles.NAME + ".info";
            public static final String STATE =                 Vehicles.NAME + ".status";
        }
    }
    
    public static class Logins
    {
        private Logins(){}
        
        public static final String NAME = "core_logins";
        
        public static final String INSERT = "INSERT INTO " + NAME + " (" + 
                                            Fields.ID + COMMA +
                                            Fields.REF_PEOPLE + COMMA +
                                            Fields.USER + COMMA +
                                            Fields.PWD + COMMA +
                                            Fields.DT_CREATION + COMMA +
                                            Fields.PROFILE +
                                            ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        
        public static final String UPDATE = "UPDATE " + NAME + " SET " + 
                                             Fields.REF_PEOPLE + " = ? " + COMMA +
                                             Fields.USER + " = ? " + COMMA +
                                             Fields.PWD + " = ? " + COMMA +
                                             Fields.DT_CREATION + " = ? " + COMMA +
                                             Fields.PROFILE + " = ? WHERE " + 
                                             Fields.ID + " = ?";

        public static final String SELECT = "SELECT " +
                                            Fields.ID + COMMA +
                                            Fields.REF_PEOPLE + COMMA +
                                            Fields.USER + COMMA +
                                            Fields.PWD + COMMA +
                                            Fields.DT_CREATION + COMMA +
                                            Fields.PROFILE +
                                            " FROM " + NAME;
        
        public static LoginFetcher fetcher = new LoginFetcher();
        
        public static Logins table = new Logins();
        
//+-------------+--------------+------+-----+---------+-------+
//| Field       | Type         | Null | Key | Default | Extra |
//+-------------+--------------+------+-----+---------+-------+
//| id          | int(11)      | NO   | PRI | NULL    |       |
//| ref_people  | int(11)      | NO   | MUL | NULL    |       |
//| user        | varchar(45)  | YES  |     | NULL    |       |
//| pwd         | varchar(200) | YES  |     | NULL    |       |
//| dt_creation | date         | YES  |     | NULL    |       |
//| profile     | smallint(6)  | YES  |     | NULL    |       |
//+-------------+--------------+------+-----+---------+-------+
        public static class Fields
        {
            public static final String ID =           Vehicles.NAME + ".id";
            public static final String REF_PEOPLE =   Vehicles.NAME + ".ref_people";
            public static final String USER =         Vehicles.NAME + ".user";
            public static final String PWD =          Vehicles.NAME + ".pwd";
            public static final String DT_CREATION =  Vehicles.NAME + ".dt_creation";
            public static final String PROFILE =      Vehicles.NAME + ".profile";
        }
    }
}
